<?php


namespace office;

use PHPExcel;
use PHPExcel_IOFactory;

class Excel
{
    /**
     * 导入数据(xlsx,xls,csv)
     * @param string $filename 文件名
     * @param array $filedList 字段
     * @param bool $isCutting 是否切割数组
     * @param int $size 切割数
     * @return array $data
     */
    public static function upload($filename, $filedList = [], $isCutting = false, $size = 2000)
    {
        $ext = pathinfo($filename, PATHINFO_EXTENSION);
        switch ($ext) {
            case 'xlsx':
                $objReader = PHPExcel_IOFactory::createReader('Excel2007');
                $objPHPExcel = $objReader->load($filename);//加载文件
                break;
            case 'xls':
                $objReader = PHPExcel_IOFactory::createReader('Excel5');
                $objPHPExcel = $objReader->load($filename);//加载文件
                break;
            case 'csv':
                $objReader = PHPExcel_IOFactory::createReader('CSV');
                $objPHPExcel = $objReader->setInputEncoding('GBK')->load($filename);//加载文件
                break;
            default:
                return [];
                break;
        }
        $highestRow = $objPHPExcel->getSheet()->getHighestRow();//取得总行数
        $highestColumn = $objPHPExcel->getSheet()->getHighestColumn();//取得总列数
        $cellList = [];
        foreach ($filedList as $k => $v) $cellList[] = self::IntToChr($k);
        if ($cellList[count($filedList) - 1] != $highestColumn) return [];
        $data = [];
        for ($i = 0; $i < $highestRow - 1; $i++) {
            for ($j = 0; $j < count($cellList); $j++) {
                $data[$i][$filedList[$j]] = $objPHPExcel->getActiveSheet()->getCell($cellList[$j] . ($i + 2))->getFormattedValue();
                if (is_object($data[$i][$filedList[$j]])) $data[$i][$filedList[$j]] = $data[$i][$filedList[$j]]->__toString();
                $data[$i][$filedList[$j]] = preg_replace("/(\s|\ \;|　|\xc2\xa0)/", "", $data[$i][$filedList[$j]]);
            }
        }
        if ($isCutting) return array_chunk($data, $size);
        return $data;
    }

    /**
     * 导出数据(xlsx,xls,csv)
     * @param array $data 导出数据
     * @param array $filedList 字段
     * @param array $title 列表头
     * @param string $filename 导出文件名
     * @param string $ext 文件后缀名
     */
    public static function export($data = [], $filedList = [], $title = [], $filename = '导出数据', $ext = 'xlsx')
    {
        $filename = $filename . '-' . date('YmdHis');
        $filetitle = iconv('UTF-8', 'GBK', $filename);
        switch ($ext) {
            case 'xlsx':
                $type = 'Excel2007';
                break;
            case 'xls':
                $type = 'Excel5';
                break;
            default:
                $type = 'CSV';
                self::exportCsv($data, $title, $filename . '.' . $ext);
                break;
        }
        $cell = self::getCellName();
        $objPHPExcel = new PHPExcel();
        $objActSheet = $objPHPExcel->getActiveSheet();
        array_unshift($data, $title);
        $i = 1;
        foreach ($data as $key) {
            foreach ($filedList as $k => $v) {
                if(is_numeric($key[$v])&&$key[$v]>1000000){
                    $objActSheet->setCellValueExplicit($cell[$k] . $i, $key[$v]);
                }else{
                    $objActSheet->setCellValue($cell[$k] . $i, $key[$v]);
                }
            }
            $i++;
        }
        ob_end_clean();//清除缓冲区,避免乱码
        header("pragma:public");
        header("Expires: 0");
        header("Content-type: text/html; charset=utf-8");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/download");
        header("Content-type:application/vnd.ms-excel;charset=utf-8;name={$filetitle}.{$ext}");
        header("Content-Disposition:attachment;filename={$filename}.{$ext}");
        header("Content-Transfer-Encoding:binary");
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $type);
        $objWriter->save('php://output');
        exit;
    }

    /**
     * CSV导出数据
     * @param array $data 导出的数据
     * @param array $titleList 文件表头
     * @param string $filename 导出文件名
     */
    protected static function exportCsv($data = [], $titleList = [], $filename = '导出数据.csv')
    {
        if (count($data) > 50000) exit;
        ob_end_clean();//清除缓冲区,避免乱码
        header("pragma:public");
        header("Expires: 0");
        header("Content-type:text/html;charset=utf-8");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/download");
        header("Content-type:application/vnd.ms-excel;charset=utf-8;name={$filename}");
        header("Content-Disposition:attachment;filename={$filename}");
        header("Cache-Control:max-age=0");
        header("Content-Transfer-Encoding:binary");
        $file = fopen('php://output', 'a');
        $limit = 1000;
        $calc = 0;
        $title = [];
        $tarr = [];
        foreach ($titleList as $v) {
            $title[] = iconv('UTF-8', 'GBK//IGNORE', $v);
        }
        fputcsv($file, $title);
        foreach ($data as $v) {
            $calc++;
            if ($limit == $calc) {
                ob_flush();
                flush();
                $calc = 0;
            }
            foreach ($v as $t) {
                $tarr[] = iconv('UTF-8', 'GBK//IGNORE', $t);
            }
            fputcsv($file, $tarr);
            unset($tarr);
        }
        fclose($file);
        exit;
    }

    /**
     * 生成Excel列标
     * @param int $index 索引值
     * @param int $start 字母起始值
     * @return string 返回字母
     */
    protected static function IntToChr($index, $start = 65)
    {
        $str = '';
        if (floor($index / 26) > 0) {
            $str .= self::IntToChr(floor($index / 26) - 1);
        }
        return $str . chr($index % 26 + $start);
    }

    /**
     * Excel列标(A~BZ)
     * @return array 返回字母
     */
    protected static function getCellName()
    {
        $cellName = [];
        $startL = 'A';
        while ($startL != 'CA') $cellName[] = $startL++;
        return $cellName;
    }
}